<?php

include 'DBconn.php';

class POSearch {

    public function cancelPoItem($VObject) {

        $quantity_cancelled = $VObject->cancelled_qty;

        $po_pro_qty = 0;
        $on_stock = 0;
        $balance_to_receive = 0;
        $po_serialnumber_fk = 0;
        $pro_serialnumber_fk = 0;
        $warehouse_serialnumber_fk = 0;

        $p_result = mysql_query("Select pd.po_serialnumber_fk,pd.pro_qty,IFNULL((Select IFNULL(Sum(gd.received_qty - IFNULL((Select IFNULL(Sum(do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.grn_detserialnumber_fk=gd.grn_detserialnumber),0)),0) from grn_detail as gd where gd.activestatus=1 
        and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) as on_stock,pd.pro_qty -  IFNULL((Select IFNULL(Sum(gd.received_qty),0) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) as balance_qty,pd.pro_serialnumber_fk,pd.warehouse_serialnumber_fk from 
        po_details as pd where pd.po_detserialnumber='$VObject->po_detserialnumber_fk'") or die(mysql_error());
        if ($p_row = mysql_fetch_object($p_result)) {
            $po_pro_qty = (int) $p_row->pro_qty;
            $on_stock = (int) $p_row->on_stock;
            $balance_to_receive = (int) $p_row->balance_qty;
            $po_serialnumber_fk = (int) $p_row->po_serialnumber_fk;
            $pro_serialnumber_fk = (int) $p_row->pro_serialnumber_fk;
            $warehouse_serialnumber_fk = (int) $p_row->warehouse_serialnumber_fk;
        }

        $sc_detserialnumberArr = array();
        $map_serialnumberArr = array();
        $balance_qtyArr = array();
        $free_allocationArr = array();

        $query = "Select pms.self_sc,pms.map_serialnumber,pms.sc_detserialnumber,pms.allocated_qty-(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber) as balance_qty 
        from po_map_sc as pms where pms.activestatus=1 and pms.po_detserialnumber='$VObject->po_detserialnumber_fk'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {

            if ($quantity_cancelled > 0) {

                $balance_qty = (int) $row->balance_qty;

                if ($balance_qty <= $on_stock) {
                    $on_stock = $on_stock - $balance_qty;
                    $balance_qty = 0;
                } else {
                    $balance_qty = $balance_qty - $on_stock;
                    $on_stock = 0;
                }
                
                 if ($balance_qty > $quantity_cancelled) {
                    $balance_qty = $quantity_cancelled;
                    $quantity_cancelled = 0;
                } else {
                    $quantity_cancelled = $quantity_cancelled - $balance_qty;
                }

                if ($balance_qty > 0) {
                    $sc_detserialnumberArr[] = (int) $row->sc_detserialnumber;
                    $map_serialnumberArr[] = (int) $row->map_serialnumber;
                    $balance_qtyArr[] = (int) $balance_qty;

                    if ($row->self_sc != 0) {
                        $free_allocationArr[] = (int) $balance_qty;
                    } else {
                        $free_allocationArr[] = 0;
                    }
                }
            } else {
                break;
            }
        }

        $tot_free_allocation_qty = 0;
        $tot_inorder_qty = 0;

        foreach ($sc_detserialnumberArr as $i => $sc_detserialnumber) {
            $s = mysql_query("Update po_map_sc set activestatus=IF((allocated_qty-$balance_qtyArr[$i])<=0,0,activestatus),allocated_qty=allocated_qty-$balance_qtyArr[$i] where map_serialnumber='$map_serialnumberArr[$i]'") or die(mysql_error());
            $s1 = mysql_query("Insert Into cancelled_po(map_serialnumber_fk,sc_detserialnumber_fk,po_detserialnumber_fk,cancelled_qty,cre_userid_fk,cre_datetime,activestatus) values('$map_serialnumberArr[$i]','$sc_detserialnumber','$VObject->po_detserialnumber_fk','$balance_qtyArr[$i]','$VObject->cre_userid_fk','$VObject->cre_datetime',1)") or die(mysql_error());
            $last_id = mysql_insert_id();
            $s2 = mysql_query("Insert Into stock_details(warehouse_serialnumber_fk,trans_detserialnumber,trans_type,pro_serialnumber,on_hand,on_sale,in_order,sold,free_for_allocation,activestatus) values('$warehouse_serialnumber_fk','$last_id','5','$pro_serialnumber_fk',0,0,'$balance_qtyArr[$i]',0,'$free_allocationArr[$i]',1)") or die(mysql_error());
            $tot_inorder_qty = $tot_inorder_qty + $balance_qtyArr[$i];
            $tot_free_allocation_qty = $tot_free_allocation_qty + $free_allocationArr[$i];
        }

        $s3 = mysql_query("Select in_order,free_for_allocation from stock_header where pro_serialnumber='$pro_serialnumber_fk' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'") or die(mysql_error());
        while ($row3 = mysql_fetch_object($s3)) {
            $in_order = (int) $row3->in_order;
            $free_for_allocation = (int) $row3->free_for_allocation;

            $in_order = $in_order - $tot_inorder_qty;
            $free_for_allocation = $free_for_allocation - $tot_free_allocation_qty;

            $s5 = mysql_query("Update stock_header set in_order='$in_order',free_for_allocation='$free_for_allocation' where pro_serialnumber='$pro_serialnumber_fk' and warehouse_serialnumber_fk='$warehouse_serialnumber_fk'") or die(mysql_error());
        }

        if ($VObject->cancelled_qty < $po_pro_qty) {
            $quantity = $po_pro_qty - $VObject->cancelled_qty;
            mysql_query("Update po_details set pro_qty='$quantity' where po_detserialnumber='$VObject->po_detserialnumber_fk'") or die(mysql_error());
            mysql_query("Update grn_detail set po_qty='$quantity' where po_detserialnumber_fk='$VObject->po_detserialnumber_fk'") or die(mysql_error());
        } else if ($po_pro_qty == $VObject->cancelled_qty) {
            mysql_query("Update po_details set activestatus=0 where po_detserialnumber='$VObject->po_detserialnumber_fk'") or die(mysql_error());
        }

        $product_array = $this->checkProductStatus($VObject->po_detserialnumber_fk);
        $Squery3 = "update po_details  set pro_status='$product_array[1]' where po_detserialnumber='$VObject->po_detserialnumber_fk' and activestatus=1";
        mysql_query($Squery3) or die(mysql_error());

        $query6 = "Select po_detserialnumber from po_details where po_serialnumber_fk='$po_serialnumber_fk' and activestatus=1";
        $result6 = mysql_query($query6) or die(mysql_error());
        $num_rows = mysql_num_rows($result6);
        if ($num_rows == 0) {
            mysql_query("Update po_header set po_status='VOID' where po_serialnumber='$po_serialnumber_fk' and activestatus=1") or die(mysql_error());
        } else {
            $this->checkPOStatus($po_serialnumber_fk);
        }

        //Update Contents On Table
        $po_total_price = 0;
        $po_total_qty = 0;
        $result7 = mysql_query("Select * from po_details where po_serialnumber_fk='$po_serialnumber_fk' and activestatus=1") or die(mysql_error());
        while ($row7 = mysql_fetch_object($result7)) {
            $pro_total_price = round(($row7->unit_price * $row7->pro_qty), 4);
            $po_total_price = $po_total_price + $pro_total_price;
            $po_total_qty = $po_total_qty + (int) $row7->pro_qty;
            mysql_query("Update po_details set pro_total_price='$pro_total_price' where po_detserialnumber='$row7->po_detserialnumber'") or die(mysql_error());
        }
        $po_total_price = round($po_total_price, 4);
        mysql_query("Update po_header set total_amount='$po_total_price',total_qty='$po_total_qty' where po_serialnumber='$po_serialnumber_fk'") or die(mysql_error());

        $return_mess = 'The selected item was cancelled';

        return $return_mess;
    }

    public function checkPOStatus($po_serialnumber) {
        $num_rows = 0;
        $status_rows = 0;
        $status_rows_part = 0;
        $po_status = "";

        $query = "Select pro_status from po_details where po_serialnumber_fk='$po_serialnumber' and activestatus=1";
        $s2 = mysql_query($query) or die(mysql_error());
        $num_rows = mysql_num_rows($s2);
        while ($row2 = mysql_fetch_object($s2)) {
            if ($row2->pro_status == "FULLY SATISFIED")
                $status_rows = $status_rows + 1;
            else if ($row2->pro_status == "PARTIALLY SATISFIED")
                $status_rows_part = $status_rows_part + 1;
        }
        if ($num_rows == $status_rows && $status_rows_part == 0)
            $po_status = "FULLY SATISFIED";
        else if (($status_rows_part > 0 || $status_rows > 0 ) && $num_rows != $status_rows)
            $po_status = "PARTIALLY SATISFIED";
        else if ($status_rows == 0 && $status_rows_part == 0)
            $po_status = "CONFIRM";

        $query2 = "update po_header set po_status='$po_status' where po_serialnumber='$po_serialnumber' and activestatus=1";
        $s2 = mysql_query($query2) or die(mysql_error());

        return $po_status;
    }

    public function checkProductStatus($po_detserialnumber_fk) {
        $received_qty = 0;
        $pro_status = "";
        $po_qty = 0;

        $product_array = array();

        $query2 = "Select received_qty,po_qty from grn_detail where po_detserialnumber_fk='$po_detserialnumber_fk'";
        $s2 = mysql_query($query2) or die(mysql_error());
        if (mysql_num_rows($s2) > 0) {
            while ($row = mysql_fetch_object($s2)) {
                $received_qty+=$row->received_qty;
                $po_qty = $row->po_qty;
            }

            $product_array[0] = $received_qty;

            if ($product_array[0] < $po_qty)
                $product_array[1] = "PARTIALLY SATISFIED";
            else if ($product_array[0] >= $po_qty)
                $product_array[1] = "FULLY SATISFIED";
        }else {
            $product_array[0] = 0;
            $product_array[1] = "OPEN";
        }
        return $product_array;
    }

    public function getCancelledPos() {
        $ret = array();
        $query = "Select ph.po_serialnumber,ph.po_id,ph.po_issue_date,ph.expected_datetime,ph.po_remarks,cur.currency_name,sup.client_name as supplier,ph.po_gst,ph.current_rate from po_header as ph LEFT JOIN client_header as sup ON sup.serialnumber=ph.supplier_serialnumber_fk LEFT JOIN 
		currency_header as cur ON cur.serialnumber=ph.currency_serialnumber_fk where ph.activestatus=1 and ph.po_serialnumber IN(Select spd.po_serialnumber_fk from po_details as spd where spd.po_detserialnumber IN(Select scp.po_detserialnumber_fk from cancelled_po as scp where scp.activestatus=1))";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPoHeaderSearch();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->po_issue_date = date("d-m-Y", strtotime($row->po_issue_date));
            $temp->expected_datetime = date("d-m-Y", strtotime($row->expected_datetime));
            $temp->po_status = "Cancelled";
            $temp->currency_name = $row->currency_name;
            $temp->supplier = $row->supplier;
            $temp->po_remarks = $row->po_remarks;

            $total_amount = 0;
            $total_qty = 0;
            $s1 = mysql_query("Select IFNULL((Select Sum(cp.cancelled_qty) from cancelled_po as cp where cp.activestatus=1 and cp.po_detserialnumber_fk=pd.po_detserialnumber),0) as pro_qty,pd.unit_price from po_details as pd where pd.po_serialnumber_fk='$row->po_serialnumber' and pd.po_detserialnumber IN(Select scp.po_detserialnumber_fk from cancelled_po as scp where scp.activestatus=1)") or die(mysql_error());
            while ($row1 = mysql_fetch_object($s1)) {
                $pro_total_price = round(($row1->pro_qty * $row1->unit_price), 4);
                $total_amount = $total_amount + $pro_total_price;
                $total_qty = $total_qty + (int) $row1->pro_qty;
            }
            $temp->total_qty = $total_qty;
            $temp->tot_balance_qty = $total_qty;
            $temp->total_amount = round($total_amount, 4);
            $temp->gst_amount = round(($total_amount * ($row->po_gst / 100)), 4);
            $temp->sgd_amount = round(($total_amount * $row->current_rate), 4);
            $temp->sgd_total_amount = round((($total_amount + $temp->gst_amount) * $row->current_rate), 4);
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getCancelledPoDetails($po_serialnumber) {
        $ret = array();
        $query = "Select ph.po_serialnumber,pd.pro_serialnumber_fk,ph.po_id,sh.sales_con_code,pd.po_detserialnumber,pd.unit_price,pd.pro_status,wh.warehouse_name,pro.pro_name,pro.part_number,pro.pro_description,IFNULL((Select Sum(cp.cancelled_qty) from cancelled_po as cp where cp.activestatus=1 and cp.po_detserialnumber_fk=pd.po_detserialnumber),0) as pro_qty,round((IFNULL((Select Sum(cp.cancelled_qty) from cancelled_po as cp where cp.activestatus=1 and cp.po_detserialnumber_fk=pd.po_detserialnumber),0) * pd.unit_price),4) as pro_total_price
		from po_details as pd LEFT JOIN  sales_conformation_details as sd ON sd.detserialnumber=pd.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN po_header as ph ON ph.po_serialnumber=pd.po_serialnumber_fk LEFT JOIN product_header as pro ON pro.serialnumber=pd.pro_serialnumber_fk LEFT JOIN warehouse_header as wh ON wh.serialnumber=pd.warehouse_serialnumber_fk where pd.po_serialnumber_fk='$po_serialnumber' and pd.po_detserialnumber IN(Select scp.po_detserialnumber_fk from cancelled_po as scp where scp.activestatus=1)";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPoDetailSearch();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->po_detserialnumber = (int) $row->po_detserialnumber;
            $temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
            $temp->pro_qty = (int) $row->pro_qty;
            $temp->unit_price = (double) $row->unit_price;
            $temp->pro_total_price = (double) $row->pro_total_price;
            $temp->pro_status = "Cancelled";
            $temp->warehouse_name = $row->warehouse_name;
            $temp->balance_qty = 0;
            $temp->cancelled_qty = 0;
            $temp->received_qty = 0;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getAllCancelledPo() {
        $ret = array();
        $query = "Select * from cancelled_po where activestatus=1";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoCancelledPo();
            $temp->serialnumber = (int) $row->serialnumber;
            $temp->po_detserialnumber_fk = (int) $row->po_detserialnumber_fk;
            $temp->cancelled_qty = (int) $row->cancelled_qty;
            $temp->cre_userid_fk = (int) $row->cre_userid_fk;
            $temp->cre_datetime = $row->cre_datetime;
            $temp->activestatus = (boolean) $row->activestatus;
            $ret[] = $temp;
        }
        return $ret;
    }

    public function getGrLinks($po_detserialnumber) {
        $ret = array();
        $query = "Select gh.gr_id,gd.received_qty,pro.pro_name,pro.part_number,pro.pro_description,ROUND(pd.unit_price,4) as unit_price,ROUND((pd.unit_price * gd.received_qty),4) as pro_total_price from  grn_detail as gd LEFT JOIN po_details as pd ON pd.po_detserialnumber=gd.po_detserialnumber_fk LEFT JOIN grn_header as gh ON gh.activestatus=1 and gh.gr_serialnumber=gd.gr_serialnumber_fk
	   LEFT JOIN product_header as pro ON pro.activestatus=1 and pro.serialnumber=gd.pro_serialnumber_fk where gd.activestatus=1 and gd.po_detserialnumber_fk='$po_detserialnumber'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoGrLinksSearch();
            $temp->gr_id = $row->gr_id;
            $temp->received_qty = (int) $row->received_qty;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $temp->unit_price = (double) $row->unit_price;
            $temp->pro_total_price = (double) $row->pro_total_price;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getRecentPOs() {
        $ret = array();
        $query = "Select ph.po_serialnumber,ph.po_id,ph.po_issue_date,ph.expected_datetime,ph.total_qty,ph.po_status,ph.po_remarks,ph.total_amount,round((ph.total_amount * (ph.po_gst/100) ),4) as gst_amount,
		round((ph.total_amount * ph.current_rate),4) as sgd_amount,round(((ph.total_amount + (ph.total_amount * (ph.po_gst/100)) ) * ph.current_rate),4) as sgd_total_amount,cur.currency_name,sup.client_name as supplier,
		IFNULL(ph.total_qty - IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk IN(Select pd.po_detserialnumber from po_details as pd where pd.po_serialnumber_fk=ph.po_serialnumber 
		and pd.activestatus=1)),0),0) as tot_balance_qty from po_header as ph LEFT JOIN client_header as sup ON sup.serialnumber=ph.supplier_serialnumber_fk LEFT JOIN currency_header as cur ON cur.serialnumber=ph.currency_serialnumber_fk where ph.activestatus=1 
		and ph.po_status!='DRAFT' and ph.po_status!='VOID' ORDER BY ph.po_serialnumber DESC LIMIT 20";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPoHeaderSearch();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->po_issue_date = date("d-m-Y", strtotime($row->po_issue_date));
            $temp->expected_datetime = date("d-m-Y", strtotime($row->expected_datetime));
            $temp->total_qty = (int) $row->total_qty;
            $temp->tot_balance_qty = (int) $row->tot_balance_qty;
            $temp->po_status = $row->po_status;
            $temp->currency_name = $row->currency_name;
            $temp->supplier = $row->supplier;
            $temp->po_remarks = $row->po_remarks;
            $temp->total_amount = (double) $row->total_amount;
            $temp->gst_amount = (double) $row->gst_amount;
            $temp->sgd_amount = (double) $row->sgd_amount;
            $temp->sgd_total_amount = (double) $row->sgd_total_amount;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getPoDetails($po_serialnumber) {
        $ret = array();
        $query = "Select ph.po_serialnumber,pd.pro_serialnumber_fk,ph.po_id,sh.sales_con_code,pd.po_detserialnumber,pd.pro_qty,pd.unit_price,round((pd.unit_price * pd.pro_qty),4) as pro_total_price,pd.pro_status,wh.warehouse_name,pro.pro_name,pro.part_number,pro.pro_description,IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) as received_qty,pd.pro_qty - IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) as balance_qty
		from po_details as pd LEFT JOIN  sales_conformation_details as sd ON sd.detserialnumber=pd.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN po_header as ph ON ph.po_serialnumber=pd.po_serialnumber_fk LEFT JOIN product_header as pro ON pro.serialnumber=pd.pro_serialnumber_fk LEFT JOIN warehouse_header as wh ON wh.serialnumber=pd.warehouse_serialnumber_fk where pd.activestatus=1 and pd.po_serialnumber_fk='$po_serialnumber'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPoDetailSearch();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->po_detserialnumber = (int) $row->po_detserialnumber;
            $temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
            $temp->pro_qty = (int) $row->pro_qty;
            $temp->unit_price = (double) $row->unit_price;
            $temp->pro_total_price = (double) $row->pro_total_price;
            $temp->pro_status = $row->pro_status;
            $temp->warehouse_name = $row->warehouse_name;
            $temp->balance_qty = (int) $row->balance_qty;
            $temp->cancelled_qty = (int) $row->balance_qty;
            $temp->received_qty = (int) $row->received_qty;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getCompletedPOs() {
        $ret = array();
        $query = "Select ph.po_serialnumber,ph.po_id,ph.po_issue_date,ph.expected_datetime,ph.total_qty,ph.po_status,ph.po_remarks,ph.total_amount,round((ph.total_amount * (ph.po_gst/100) ),4) as gst_amount,
		round((ph.total_amount * ph.current_rate),4) as sgd_amount,round(((ph.total_amount + (ph.total_amount * (ph.po_gst/100)) ) * ph.current_rate),4) as sgd_total_amount,cur.currency_name,sup.client_name as supplier,
		IFNULL(ph.total_qty - IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk IN(Select pd.po_detserialnumber from po_details as pd where pd.po_serialnumber_fk=ph.po_serialnumber 
		and pd.activestatus=1)),0),0) as tot_balance_qty from po_header as ph LEFT JOIN client_header as sup ON sup.serialnumber=ph.supplier_serialnumber_fk LEFT JOIN currency_header as cur ON cur.serialnumber=ph.currency_serialnumber_fk where ph.activestatus=1 
		and ph.po_serialnumber IN(Select pd.po_serialnumber_fk from po_details as pd where pd.activestatus=1 and pd.pro_status='FULLY SATISFIED')";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPoHeaderSearch();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->po_issue_date = date("d-m-Y", strtotime($row->po_issue_date));
            $temp->expected_datetime = date("d-m-Y", strtotime($row->expected_datetime));
            $temp->total_qty = (int) $row->total_qty;
            $temp->tot_balance_qty = (int) $row->tot_balance_qty;
            $temp->po_status = $row->po_status;
            $temp->currency_name = $row->currency_name;
            $temp->supplier = $row->supplier;
            $temp->po_remarks = $row->po_remarks;
            $temp->total_amount = (double) $row->total_amount;
            $temp->gst_amount = (double) $row->gst_amount;
            $temp->sgd_amount = (double) $row->sgd_amount;
            $temp->sgd_total_amount = (double) $row->sgd_total_amount;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getCompletedPoDetails($po_serialnumber) {
        $ret = array();
        $query = "Select ph.po_serialnumber,pd.pro_serialnumber_fk,ph.po_id,sh.sales_con_code,pd.po_detserialnumber,pd.pro_qty,pd.unit_price,round((pd.unit_price * pd.pro_qty),4) as pro_total_price,pd.pro_status,wh.warehouse_name,pro.pro_name,pro.part_number,pro.pro_description,IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) as received_qty,pd.pro_qty - IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) as balance_qty
		from po_details as pd LEFT JOIN  sales_conformation_details as sd ON sd.detserialnumber=pd.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN po_header as ph ON ph.po_serialnumber=pd.po_serialnumber_fk LEFT JOIN product_header as pro ON pro.serialnumber=pd.pro_serialnumber_fk LEFT JOIN warehouse_header as wh ON wh.serialnumber=pd.warehouse_serialnumber_fk where pd.activestatus=1 and pd.po_serialnumber_fk='$po_serialnumber' and pd.pro_status='FULLY SATISFIED'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPoDetailSearch();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->po_detserialnumber = (int) $row->po_detserialnumber;
            $temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
            $temp->pro_qty = (int) $row->pro_qty;
            $temp->unit_price = (double) $row->unit_price;
            $temp->pro_total_price = (double) $row->pro_total_price;
            $temp->pro_status = $row->pro_status;
            $temp->warehouse_name = $row->warehouse_name;
            $temp->balance_qty = (int) $row->balance_qty;
            $temp->cancelled_qty = (int) $row->balance_qty;
            $temp->received_qty = (int) $row->received_qty;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getConformedPOs() {
        $ret = array();
        $query = "Select ph.po_serialnumber,ph.po_id,ph.po_issue_date,ph.expected_datetime,ph.total_qty,ph.po_status,ph.po_remarks,ph.total_amount,round((ph.total_amount * (ph.po_gst/100) ),4) as gst_amount,
		round((ph.total_amount * ph.current_rate),4) as sgd_amount,round(((ph.total_amount + (ph.total_amount * (ph.po_gst/100)) ) * ph.current_rate),4) as sgd_total_amount,cur.currency_name,sup.client_name as supplier,
		IFNULL(ph.total_qty - IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk IN(Select pd.po_detserialnumber from po_details as pd where pd.po_serialnumber_fk=ph.po_serialnumber 
		and pd.activestatus=1)),0),0) as tot_balance_qty from po_header as ph LEFT JOIN client_header as sup ON sup.serialnumber=ph.supplier_serialnumber_fk LEFT JOIN currency_header as cur ON cur.serialnumber=ph.currency_serialnumber_fk where 
		ph.activestatus=1 and ph.po_status!='DRAFT' and ph.po_status!='VOID' and ph.po_serialnumber IN(Select pd.po_serialnumber_fk from po_details as pd where pd.activestatus=1 and pd.pro_status='OPEN')";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPoHeaderSearch();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->po_issue_date = date("d-m-Y", strtotime($row->po_issue_date));
            $temp->expected_datetime = date("d-m-Y", strtotime($row->expected_datetime));
            $temp->total_qty = (int) $row->total_qty;
            $temp->tot_balance_qty = (int) $row->tot_balance_qty;
            $temp->po_status = $row->po_status;
            $temp->currency_name = $row->currency_name;
            $temp->supplier = $row->supplier;
            $temp->po_remarks = $row->po_remarks;
            $temp->total_amount = (double) $row->total_amount;
            $temp->gst_amount = (double) $row->gst_amount;
            $temp->sgd_amount = (double) $row->sgd_amount;
            $temp->sgd_total_amount = (double) $row->sgd_total_amount;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getConformedPoDetails($po_serialnumber) {
        $ret = array();
        $query = "Select ph.po_serialnumber,pd.pro_serialnumber_fk,ph.po_id,sh.sales_con_code,pd.po_detserialnumber,pd.pro_qty,pd.unit_price,round((pd.unit_price * pd.pro_qty),4) as pro_total_price,pd.pro_status,wh.warehouse_name,pro.pro_name,pro.part_number,pro.pro_description,IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) as received_qty,pd.pro_qty - IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) as balance_qty
		from po_details as pd LEFT JOIN  sales_conformation_details as sd ON sd.detserialnumber=pd.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN po_header as ph ON ph.po_serialnumber=pd.po_serialnumber_fk LEFT JOIN product_header as pro ON pro.serialnumber=pd.pro_serialnumber_fk LEFT JOIN warehouse_header as wh ON wh.serialnumber=pd.warehouse_serialnumber_fk where pd.activestatus=1 and pd.po_serialnumber_fk='$po_serialnumber' and pd.pro_status='OPEN'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPoDetailSearch();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->po_detserialnumber = (int) $row->po_detserialnumber;
            $temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
            $temp->pro_qty = (int) $row->pro_qty;
            $temp->unit_price = (double) $row->unit_price;
            $temp->pro_total_price = (double) $row->pro_total_price;
            $temp->pro_status = $row->pro_status;
            $temp->warehouse_name = $row->warehouse_name;
            $temp->balance_qty = (int) $row->balance_qty;
            $temp->cancelled_qty = (int) $row->balance_qty;
            $temp->received_qty = (int) $row->received_qty;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getOutStandingPOs() {
        $ret = array();
        $query = "Select ph.po_serialnumber,ph.po_id,ph.po_issue_date,ph.expected_datetime,ph.total_qty,ph.po_status,ph.po_remarks,ph.total_amount,round((ph.total_amount * (ph.po_gst/100) ),4) as gst_amount,
		round((ph.total_amount * ph.current_rate),4) as sgd_amount,round(((ph.total_amount + (ph.total_amount * (ph.po_gst/100)) ) * ph.current_rate),4) as sgd_total_amount,cur.currency_name,sup.client_name as supplier,
		IFNULL(ph.total_qty - IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk IN(Select pd.po_detserialnumber from po_details as pd where pd.po_serialnumber_fk=ph.po_serialnumber 
		and pd.activestatus=1)),0),0) as tot_balance_qty from po_header as ph LEFT JOIN client_header as sup ON sup.serialnumber=ph.supplier_serialnumber_fk LEFT JOIN currency_header as cur ON cur.serialnumber=ph.currency_serialnumber_fk where ph.activestatus=1 
		and ph.po_serialnumber IN(Select pd.po_serialnumber_fk from po_details as pd where pd.activestatus=1 and pd.pro_status='PARTIALLY SATISFIED')";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPoHeaderSearch();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->po_issue_date = date("d-m-Y", strtotime($row->po_issue_date));
            $temp->expected_datetime = date("d-m-Y", strtotime($row->expected_datetime));
            $temp->total_qty = (int) $row->total_qty;
            $temp->tot_balance_qty = (int) $row->tot_balance_qty;
            $temp->po_status = $row->po_status;
            $temp->currency_name = $row->currency_name;
            $temp->supplier = $row->supplier;
            $temp->po_remarks = $row->po_remarks;
            $temp->total_amount = (double) $row->total_amount;
            $temp->gst_amount = (double) $row->gst_amount;
            $temp->sgd_amount = (double) $row->sgd_amount;
            $temp->sgd_total_amount = (double) $row->sgd_total_amount;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

    public function getOutstandingPoDetails($po_serialnumber) {
        $ret = array();
        $query = "Select ph.po_serialnumber,pd.pro_serialnumber_fk,ph.po_id,sh.sales_con_code,pd.po_detserialnumber,pd.pro_qty,pd.unit_price,round((pd.unit_price * pd.pro_qty),4) as pro_total_price,pd.pro_status,wh.warehouse_name,pro.pro_name,pro.part_number,pro.pro_description,IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) as received_qty,pd.pro_qty - IFNULL((Select Sum(gd.received_qty) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) as balance_qty
		from po_details as pd LEFT JOIN  sales_conformation_details as sd ON sd.detserialnumber=pd.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN po_header as ph ON ph.po_serialnumber=pd.po_serialnumber_fk LEFT JOIN product_header as pro ON pro.serialnumber=pd.pro_serialnumber_fk LEFT JOIN warehouse_header as wh ON wh.serialnumber=pd.warehouse_serialnumber_fk where pd.activestatus=1 and pd.po_serialnumber_fk='$po_serialnumber' and pd.pro_status='PARTIALLY SATISFIED'";
        $s = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($s)) {
            $temp = new VoPoDetailSearch();
            $temp->po_serialnumber = (int) $row->po_serialnumber;
            $temp->po_id = $row->po_id;
            $temp->sales_con_code = $row->sales_con_code;
            $temp->po_detserialnumber = (int) $row->po_detserialnumber;
            $temp->pro_serialnumber_fk = (int) $row->pro_serialnumber_fk;
            $temp->pro_qty = (int) $row->pro_qty;
            $temp->unit_price = (double) $row->unit_price;
            $temp->pro_total_price = (double) $row->pro_total_price;
            $temp->pro_status = $row->pro_status;
            $temp->warehouse_name = $row->warehouse_name;
            $temp->balance_qty = (int) $row->balance_qty;
            $temp->cancelled_qty = (int) $row->balance_qty;
            $temp->received_qty = (int) $row->received_qty;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $ret[] = $temp;
        }
        mysql_free_result($s);
        return $ret;
    }

}

class VoPoDetailSearch {

    var $po_serialnumber;
    var $po_id;
    var $sales_con_code;
    var $po_detserialnumber;
    var $pro_serialnumber_fk;
    var $pro_qty;
    var $unit_price;
    var $pro_total_price;
    var $pro_status;
    var $warehouse_name;
    var $balance_qty;
    var $received_qty;
    var $pro_name;
    var $part_number;
    var $pro_description;

}

class VoGrLinksSearch {

    var $gr_id;
    var $received_qty;
    var $pro_name;
    var $part_number;
    var $pro_description;
    var $unit_price;
    var $pro_total_price;

}

class VoPoHeaderSearch {

    var $po_serialnumber;
    var $po_id;
    var $po_issue_date;
    var $expected_datetime;
    var $total_qty;
    var $tot_balance_qty;
    var $po_status;
    var $currency_name;
    var $po_remarks;
    var $total_amount;
    var $gst_amount;
    var $sgd_amount;
    var $sgd_total_amount;

}

class VoCancelledPo {

    var $serialnumber;
    var $po_detserialnumber_fk;
    var $cancelled_qty;
    var $cre_userid_fk;
    var $cre_datetime;
    var $activestatus;

}

// $e = new POSearch();
// $V = new VoCancelledPo();
// $V->po_detserialnumber_fk = 1;
// $V->cancelled_qty = 100;
// $V->cre_userid_fk = 1;
// $V->cre_datetime = 1;
// $e->cancelPoItem($V)
?>